package utils;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.*;

public class DBUtils {
    // 连接信息
    private static String driverName;
    private static String url;
    private static String username;
    private static String password;
    private final static Logger logger = LogManager.getLogger(DBUtils.class);

    static {
        // 初始化连接信息
        Properties props = new Properties();
        try {
            props.load(new FileReader("src/test/java/conf/db.properties"));
            driverName = props.getProperty("driverName");
            url = props.getProperty("url");
            username = props.getProperty("username");
            password = props.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }
        // 注册驱动
        try {
            // 通过反射，注册驱动
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            logger.error("注册数据库驱动异常");
            e.printStackTrace();
        }
    }

    // jdbc对象
    private Connection conn = null;

    // 获取连接
    private Connection getConnection() {
        try {
            // 建立连接
            conn = DriverManager.getConnection(url, username, password);
            logger.info("获取数据库连接");
        } catch (SQLException e) {
            logger.error("获取数据库连接异常");
            throw new RuntimeException(e);
        }
        return conn;
    }

    private void closeConnection() {
        if (conn != null) {
            try {
                // 关闭连接
                conn.close();
                logger.info("关闭数据库连接");
            } catch (SQLException e) {
                logger.error("关闭数据库连接异常");
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取List类型的查询结果集
     *
     * @param sql 要执行的sql语句
     * @return List 查询结果
     */
    public List<Map<String, String>> getQureyList(String sql) {
        conn = getConnection();
        List<Map<String, String>> resultList = new ArrayList<>();
        try {
            logger.info("执行sql语句：" + sql);
            ResultSet resultSet = conn.createStatement().executeQuery(sql);
            int colCount = resultSet.getMetaData().getColumnCount();
            while (resultSet.next()) {
                Map<String, String> map = new HashMap<>();
                for (int i = 1; i <= colCount; i++) {
                    String colName = resultSet.getMetaData().getColumnLabel(i).toLowerCase();
                    String colValue = resultSet.getString(colName);
                    map.put(colName, colValue);
                }
                resultList.add(map);
            }
            resultSet.close();
        } catch (SQLException e) {
            logger.error("执行sql语句发生异常");
            e.printStackTrace();
        } finally {
            closeConnection();
        }
        return resultList;
    }

    /**
     * 获取JSONArray类型的查询结果集
     *
     * @param sql 要执行的sql语句
     * @return JSONArray 查询结果
     */
    public JSONArray getQureyJsonArray(String sql) {
        conn = getConnection();
        JSONArray jsonArray = new JSONArray();
        try {
            logger.info("执行sql语句：" + sql);
            ResultSet resultSet = conn.createStatement().executeQuery(sql);
            int colCount = resultSet.getMetaData().getColumnCount();
            while (resultSet.next()) {
                JSONObject jsonObject = new JSONObject();
                for (int i = 1; i <= colCount; i++) {
                    String colName = resultSet.getMetaData().getColumnLabel(i).toLowerCase();
                    Object colValue = resultSet.getObject(i);
                    jsonObject.put(colName, colValue);
                }
                jsonArray.add(jsonObject);
            }
            resultSet.close();
        } catch (SQLException e) {
            logger.error("执行sql语句发生异常");
            e.printStackTrace();
        } finally {
            closeConnection();
        }
        return jsonArray;
    }

    /**
     * 获取JSONObject类型的查询结果，适用于结果为单条数据
     *
     * @param sql 要执行的sql语句
     * @return JSONObject查询结果
     */
    public JSONObject getQureyJson(String sql) {
        conn = getConnection();
        JSONObject jsonObject = new JSONObject();
        try {
            logger.info("执行sql语句：" + sql);
            ResultSet resultSet = conn.createStatement().executeQuery(sql);
            int colCount = resultSet.getMetaData().getColumnCount();
            while (resultSet.next()) {
                for (int i = 1; i <= colCount; i++) {
                    String colName = resultSet.getMetaData().getColumnLabel(i).toLowerCase();
                    Object colValue = resultSet.getObject(i);
                    jsonObject.put(colName, colValue);
                }
            }
            resultSet.close();
        } catch (SQLException e) {
            logger.error("执行sql语句发生异常");
            e.printStackTrace();
        } finally {
            closeConnection();
        }
        return jsonObject;
    }

    /**
     * 更新、插入操作
     *
     * @param sql 要执行的sql语句
     * @return 影响的行数
     * @throws SQLException sql异常
     */
    public int update(String sql) throws SQLException {
        conn = getConnection();
        int rowCount = 0;
        try {
            logger.info("执行sql语句：" + sql);
            rowCount = conn.createStatement().executeUpdate(sql);
        } catch (SQLException e) {
            conn.rollback();
            logger.error("执行sql语句发生异常");
            e.printStackTrace();
        } finally {
            closeConnection();
        }
        return rowCount;
    }

    /**
     * 获取查询集指定行指定key的值
     *
     * @param sql      要执行的sql语句
     * @param rowIndex 要获取的行索引，从0开始
     * @param key      指定的key
     * @return 键为key的值
     */
    public String getRowValue(String sql, int rowIndex, String key) {
        List<Map<String, String>> resultList = this.getQureyList(sql);
        return resultList.get(rowIndex).get(key);
    }

    public static void main(String[] args) throws SQLException {
        String sql = "select * from tb_lybs where id=72;";
        DBUtils db = new DBUtils();
        String rs = db.getQureyJson(sql).toJSONString();
        System.out.println(rs);
        String title = db.getRowValue(sql, 0, "title");
        System.out.println(title);
        JSONArray jsonArray = db.getQureyJsonArray(sql);
        System.out.println(jsonArray.get(0).toString());
        String sql2 = "update tb_lybs set content=\"作者是admin，今天是星期日，现在的时间是2022-01-02 22:43:55\" where id=72;";
        DBUtils db2 = new DBUtils();
        int n = db2.update(sql2);
        System.out.println(n);
    }
}
